﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using StoreManagementSystem.DomainObjects;
using System.Data;

namespace StoreManagementSystem.DataAccessLayer
{
    public class SQLAisleDAO : IDataAccessObject<Entity>
    {
        private ISQLDataProvider DataProvider { get; set; }
        public SQLAisleDAO(ISQLDataProvider SQLDataProvider) 
        {
            DataProvider = SQLDataProvider;
        }

        public void store(Entity e)
        {
            if (e is Aisle)
            {
                Aisle aisle = (Aisle)e;
                List<SQLParam> dbparams = new List<SQLParam>();
                dbparams.Add(new SQLParam("?id", aisle.id));
                dbparams.Add(new SQLParam("?name", aisle.Name));

                // build the parameter string to pass in values
                StringBuilder paramstr = new StringBuilder();
                for (int i = 0; i < dbparams.Count - 1; i++)
                {
                    paramstr.Append(dbparams[i].Name + ",");
                }
                paramstr.Append(dbparams[dbparams.Count - 1].Name);

                string SQL = "INSERT INTO Aisle (id,Name)" +
                        "VALUES(" + paramstr.ToString() + ");";
                DataProvider.Open();
                DataProvider.ExecuteSQL(SQL, dbparams, false);
                DataProvider.Close();
            }
        }

        public DomainObjects.Entity get(params string[] parameters)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            string SQL = "";
            string Name = "";

            Name = parameters[0];
            dbparams.Add(new SQLParam("?Name", Name));

            SQL = "SELECT * FROM aisles WHERE Name=?Name";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();
            Aisle aisle = new Aisle();
            aisle.id = dt.Rows[0]["id"].ToString();
            aisle.Name = dt.Rows[0]["Name"].ToString();
            aisle.CategoryListLeft = getLeft(aisle.id);
            aisle.CategoryListRight = getRight(aisle.id);
            return aisle;
        }

        public List<Aisle> getAisles()
        {
            List<Aisle> aisles = new List<Aisle>();
            string SQL = "SELECT * FROM aisles";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, true);
            DataProvider.Close();
            foreach (DataRow dr in dt.Rows)
            {
                Aisle aisle = new Aisle(dr["id"].ToString(), dr["name"].ToString());
                aisle.CategoryListLeft = getLeft(dr["id"].ToString());
                aisle.CategoryListRight = getRight(dr["id"].ToString());
                aisles.Add(aisle);
            }
            return aisles;
        }

        public List<Category> getLeft(string id)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            dbparams.Add(new SQLParam("?id", id));
            List<Category> left = new List<Category>();
            string SQL = "SELECT left_aisle FROM aisles WHERE id=?id";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            if (dt.Rows.Count == 0)
                return null;
            DataProvider.Close();
            string[] cats = dt.Rows[0]["left_aisle"].ToString().Split(',');
            SQLDAOFactory factory = new SQLDAOFactory(DataProvider);
            IDataAccessObject<Entity> e = factory.createDAO("category");
            if (e is SQLCategoryDAO)
            {
                SQLCategoryDAO catDAO = (SQLCategoryDAO)e;
                foreach (string cat in cats)
                    left.Add(catDAO.getById(cat));
            }
            return left;
        }

        public List<Category> getRight(string id)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            dbparams.Add(new SQLParam("?id", id));
            List<Category> right = new List<Category>();
            string SQL = "SELECT right_aisle FROM aisles WHERE id=?id";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            if (dt.Rows.Count == 0)
                return null;
            DataProvider.Close();
            string[] cats = dt.Rows[0]["right_aisle"].ToString().Split(',');
            SQLDAOFactory factory = new SQLDAOFactory(DataProvider);
            IDataAccessObject<Entity> e = factory.createDAO("category");
            if (e is SQLCategoryDAO)
            {
                SQLCategoryDAO catDAO = (SQLCategoryDAO)e;
                foreach (string cat in cats)
                    right.Add(catDAO.getById(cat));
            }
            return right;
        }


        Entity IDataAccessObject<Entity>.get(params string[] parameters)
        {
            throw new NotImplementedException();
        }

        public void create(Entity e)
        {
            throw new NotImplementedException();
        }

        public void remove(Entity e)
        {
            throw new NotImplementedException();
        }

        public void update(Entity e)
        {
            throw new NotImplementedException();
        }
    }
}